[Top]

Overview

Description

A series of PL/PGSQL (Procedural Language - PostgreSQL) functions have been written to support Raster processing and analysis. These functions have been written to be flexible and to handle a multitude of scenarios where Rasters need to be processed including from Vector, Clipped, Filtered and/or Reclassed according to particular needs. The functions output Rasters that align with the BC Raster Grid standard. (The standard being used by FAIB for aligning rasters to a consistent grid provinically to allow for province-wide compilation and analysis of rasters.)

These functions have been created to support the Caribou Recovery work and the Caribou Land-Use Simulator (CLUS). However, the hope is that they should have broader application throught FAIB for working with Raster data in PostgreSQL.

Installation

The functions reside in an .SQL file. That file can be loaded into an SQL Window in PG Admin and then all the text can be selected an executed.
The SQL file lives here:

(Right Click and Copy Link - Paste into Windows Explorer)

\\spatialfiles2.bcgov\work\FOR\VIC\HTS\ANA\Workarea\mwfowler\CLUS\Scripts\SQL\Raster

FAIB_RASTER_FUNCTIONS.sql

Steps

  1. Open an SQL Window on the user/db that you want the functions installed to
  2. Ctl+Home + Ctl+End - Selects all the Text.
  3. F5 - Executes the code.
  4. Boom - you are done. Functions are installed.

Top

Main Raster Functions

FAIB_FC_TO_RASTER

Description:

This function convert an existing Postgres feature class table to a Raster.

The output raster will align with BC Raster Grid standard and be in BC Albers (SRID:3005) projection.

Assumptions & Cautionary Notes:

If output raster already exists it will be dropped then recreated with the new raster from this function - be careful!!

The funtion will automatically generate a VAT to apply to the output raster if no VAT is supplied and the valFld is Character type. This auto generated VAT will have the name of the output Raster with _VAT tagged to the end. If this object already exists in the Database it will be dropped and overwritten. Handle with Care.

Parameters:

Name Type Default Description
Required Parameters
fc VARCHAR N/A Source feature class table
valFld VARCHAR N/A The value field in the source feature class to rasterize on if Numeric type. If no VAT is supplied and this field is Character type then a VAT will be generated and applied to the output raster. If a VAT is supplied this field must exist/join to the VAT where the VAL column from the VAT will be applied to the output raster
outRaster VARCHAR N/A The name of the output Raster
Optional Parameters
vat VARCHAR NULL The value attribute table to apply to output raster values, if applicable. VAT must have column named VAL and a columun that joins to to valFld. If no VAT is supplied (NULL) and the valFld is Character type then a VAT will be generated on the fly and applied to the output raster. The autogenerated VAT will be named the same as the outRaster with _VAT on the end.
rastSize NUMERIC 100 The size of raster pixels
rastPixType VARCHAR 32BF The output raster pixel data type
noData NUMERIC 0 NoData value in output raster
tile BOOLEAN FALSE Whether to tile the output raster

Usage & Examples:

Create a raster from a vector table using an integer (number) field to define output values.

Create a raster from a vector tagble using a character field. A VAT is not supplied to map the output raster values and the function will create one on the fly and apply the values to the output raster. The created VAT will have the name of the output with _VAT tagged onto it.

Create a raster from a vector table using VAT to define the output values. The VAT must have a VAL column that defines the output values and must link to the VAT with the valFld argument provided (TSA_NAME in the example)

Top

FAIB_RASTER_CLIP

Description:

This function will clip an existing raster by the geometry that you supply to it. The geometry can be a select GEOMETRY from another feature class table in Postgres. This function is overloaded and will also accept a WKT Polygon reprsentation to clip by. See examples.

The output raster will align with BC Raster Grid standard.

Assumptions & Cautionary Notes:

The function assumes that the source Raster is in BC Albers projection (SRID: 3005).
If output raster already exists it will be dropped then recreated with the new raster from this function - be careful!!

Implementation:

This is an overloaded function - there are 2 different ways to execute this function. Same function name call but you can use either a text Polygon Constructor or a Geometry to define the Area of Interest - drawPoly

Note: drawPoly must be a text Polygon Constructor. PostGIS Well Known Text

Parameters:

Name Type Default Description
Required Parameters
outRaster VARCHAR N/A Output Raster Name
srcRaster VARCHAR N/A The source Raster to clip from
clipper GEOMETRY or TEXT WKT Polygon ’*’ The Geometry to clip the source Raster by or a WKT Polygon text

FAIB_RASTER_FROM_VECTOR

Description:

This function is designed to generate a raster from a source vector table within an area of interest (drawPoly).

The area of interest can be a WGS 84 Well Known Text Polygon constructor (as would be created by a use in the CLUS Caribou App) or can be a Geometry object supplied by a query to another vector table.

The function allows you to generate a mask (1 value) raster output or generate values using a Numeric field from the vector source or with a Value Attribute Table.

You can further supply a where clause to filter what features from the source vector table that you would like to have included in the output raster.

See examples for the possibilities.

The output raster will align with BC Raster Grid standard.

Assumptions & Cautionary Notes:

The function assumes that the source Raster is in BC Albers projection (SRID: 3005).

If using a WKT Text Polygon as the drawPoly, it must be composed with coordinates in WGS 84 (SRID:4326)

If output raster already exists it will be dropped then recreated with the new raster from this function - be careful!!

If including a whereClause with enclosed strings in the criteria you must put a space at the end to enable proper quote embedding.
For example: whereClause:=‘ZONE_ALL LIKE’‘ESSF%’’ ’ (NOT - whereClause:=‘ZONE_ALL LIKE’‘ESSF%’’’)

Parameters:

Name Type Default Description
Required Parameters
outRaster VARCHAR N/A Output Raster Name
drawPoly TEXT (WKT Polygon) or GEOMETRY N/A The area of interest, represented as a WKT Polygon text string or a Geometry
srcVect VARCHAR N/A The source vector table to generate the raster from
Optional Parameters
whereClause VARCHAR ’*’ A Where clause to define what we want to include in the output from the source, within the area of interest
vatFld VARCHAR NULL The field in the source vector and VAT to join to
vat VARCHAR NULL The name of the VAT
mask BOOLEAN FALSE Whether to Mask the output. Mask will create an output with values of 1 where criteria met, within AOI. False will retain values from VAT
rastSize NUMERIC 100 Size of output raster cells
rastPixType VARCHAR 32BF Pixel type of output raster
noData NUMERIC 0 No Data value
tile BOOLEAN False Whether to tile the ouptut raster

Usage & Examples:

Examples using a WKT Polygon for AOI

Generate an output raster with a WKT Polygon using a Character field. VAT is automatically generated and applied to output. All values (*) are retained in the output.

Generate an output raster with a WKT Polygon using the VAT from the above example. Use a where clause to select values based on a selection from the associated VAT.

Generate an output raster with a WKT Polygon using the VAT from the above example. Use a where clause to select values based on a selection from the associated VAT but Mask the output.

Generate an output raster with a WKT Polygon and Mask the output. All values (*) are retained in the output.

Examples using a Geometry for AOI

Generate an output raster with a Geometry as AOI with a Character field for rasterizing. A VAT will automatically be generated with the name of the output raster with _VAT tagged on the end. Include all features within the AOI using the wildcard * whereClause.

Generate an output raster with a Geometry as AOI (multiple geometries unioned) with a VAT lookup and whereClause to filter the output.

Generate an output raster with a Geometry as AOI (multiple geometries unioned) with a VAT lookup and whereClause to filter the output. Mask the output.

Generate an output raster with a Geometry as AOI with and Mask the entire output area.

Top

FAIB_RASTER_FROM_RASTER

Description:

This function is designed to generate a raster from a existing source raster within an area of interest (drawPoly).

The area of interest can be a WGS 84 Well Known Text Polygon constructor (as would be created by a use in the CLUS Caribou App) or can be a Geometry object supplied by a query to another vector table.

The function allows you to generate a mask (1 value) raster output or generate values using a Value Attribute Table (VAT) to map the output values.

You can further supply a where clause to filter what features from the source vector table that you would like to have included in the output raster. The where clause can be related to the VAT that you supply to filter for values or you can use a list of raster numeric values or a range of values or a combination therof. (1, 2, 3) or (1, 2, 3, 10-40, 50-90) or (1-10) etc.

See examples for the possibilities.

The output raster will align with BC Raster Grid standard.

Assumptions & Cautionary Notes:

The function assumes that the source Raster is in BC Albers projection (SRID: 3005).

If using a WKT Text Polygon as the drawPoly, it must be composed with coordinates in WGS 84 (SRID:4326)

If output raster already exists it will be dropped then recreated with the new raster from this function - be careful!!

If including a whereClause with enclosed strings in the criteria you must put a space at the end to enable proper quote embedding.
For example: whereClause:=‘ZONE_ALL LIKE’‘ESSF%’’ ’ (NOT - whereClause:=‘ZONE_ALL LIKE’‘ESSF%’’’)

Parameters:

Name Type Default Description
Required Parameters
outRaster VARCHAR N/A Output Raster Name
drawPoly TEXT (WKT Polygon) or GEOMETRY N/A The area of interest, represented as a WKT Polygon text string or a Geometry
srcRast VARCHAR N/A The source raster to generate the raster from
Optional Parameters
rastVal VARCHAR ’*’ The value or list of values from the source raster to include in the output. If using a VAT you can form this parameter as a Where clause to define what we want to include in the output from the source, within the area of interest
rastVAT VARCHAR NULL The VAT to apply for using a Where Clause in the rastVal parameter
mask BOOLEAN FALSE Whether to Mask the output. Mask will create an output with values of 1 where criteria met, within AOI. False will retain values from VAT
rastSize NUMERIC 100 Size of output raster cells
rastPixType VARCHAR 32BF Pixel type of output raster
noData NUMERIC 0 No Data value
tile BOOLEAN False Whether to tile the ouptut raster

Usage & Examples:

Examples using a WKT Polygon for AOI

Generate an output raster with a WKT Polygon with no filtering output.

Generate an output raster with a WKT Polygon retaining all values.

Generate an output raster with a WKT Polygon querying the VAT for ESSF and ICH zones and retaining source raster values.

Generate an output raster with a WKT Polygon querying the VAT for ESSF and ICH zones and masking.

Generate an output raster with a WKT Polygon and retain all the values from the source raster within that area.

Generate an output raster with a WKT Polygon and select for particular values and a range of values using the rastVAl parameter.

When querying for source raster values you can use individual values or a range of values.

Examples:
rastVal:= ’*’
rastVal:= ‘4, 8, 48’
rastVal:= ‘4, 8-10, 12, 30-35’
etc.

Generate an output raster using the geometry from another vector table as the area of interest. Masking the output. No query filter (rastVAl) applied.

Generate an output raster using the geometry from another vector table as the area of interest. Retain source values in the output. No query filter (rastVal) applied.

Generate an output raster using the geometry from another vector table as the area of interest. Retain source values in the output. Query for Zones ICH and ESSF using a related VAT.

Generate an output raster using the geometry from another vector table as the area of interest. Mask the values in the output. Query for Zones ICH and ESSF using a related VAT.

Generate an output raster using the geometry from another vector table as the area of interest. Mask the values in the output. Query for Zones ICH and ESSF using a related VAT.

Top

Other Functions

Theese are some other functions that support the Main functions but might have some utility on their own outside the dependency of the above functions.

FAIB_GET_GEOMETRY_COLUMN

Description:

This function will return the name of the geometry column for the supplied table name.

Assumptions & Cautionary Notes:

N/A

Parameters:

Name Type Default Description
Required Parameters
fcName VARCHAR N/A The name of the feature class table you want the Geometry Column name of

FAIB_GET_RASTER_COLUMN

Description:

This function will return the name of the raster column for the supplied table name.

Assumptions & Cautionary Notes:

N/A

Parameters:

Name Type Default Description
Required Parameters
raster VARCHAR N/A The name of the raster table you want the Raster Column name of

FAIB_CREATE_VAT

Description:

This function will generate a Value Atrribute Table using the source table and field specified. The function groups the source table on the field supplied and then generates a sequential, unique number for each grouping into the output table name. The output table will contain a VAL field with the numerica value assigned to the source field group, will have a ‘Source_Table’ field track the source of the output data and will have the vatFld supplied.

Assumptions & Cautionary Notes:

Will overwrite any existing output table name.

Parameters:

Name Type Default Description
Required Parameters
srcTab VARCHAR N/A The name of the source table to generate the VAT from
vatFld VARCHAR N/A The name of the field in the Source Table to genrate the VAT from
outTab VARCHAR N/A The name of the output VAT table

FAIB_GET_BC_RASTER_ORIGIN

Description:

This function is used by the FAIB_FC_TO_RASTER function and is used to return an origin from a source vector feature class that will align with the BC Standard Raster grid. Raster functions in PosgGIS use and Upper Left (UL) origin but this function can generate a BC standard origin for Upper Left (UL), Lower Left (LL), Upper Right (UR) or Lower Right (LR) origins as well. If those are needed for some reason!

Assumptions & Cautionary Notes:

Returns a Numeric array. Index 1 is X value, Index 2 is Y value

Parameters:

Name Type Default Description
Required Parameters
fc VARCHAR N/A The name of the source vector feature class table to generate origin from
geom VARCHAR N/A The name of the geometry field in the Source Table
origin CHAR(2) UL The origin that you want returned. UL, UR, LL, LR

FAIB_GET_BC_RASTER_COORDVAL

Description:

This function is used by the FAIB_GET_BC_RASTER_ORIGIN function. This functions takes a coordinate value and will either ‘SHRINK’ or ‘GROW’ the value to create a value that is nearest to align with the BC Raster Grid Standard.

Assumptions & Cautionary Notes:

N/A

Parameters:

Name Type Default Description
Required Parameters
coord NUMERIC N/A The coordinate value to align
type VARCHAR SHRINK Whether to Grow or Shrink the value. This would be dependent on whether you are trying to align to a UL, LL, UR, LR orign type and whether the coordinate is the X or the Y.

FAIB_R_RASTERINFO

Description:

This function is deisnged to be used/called from R. It returns a numeric array of data that can be used to create a raster layer object in R from the source raster provided. The values returned in the array are: XMax, XMin, YMax, YMin, Number of Columns, Number of Rows, then a list of all the values of the raster.

Assumptions & Cautionary Notes:

N/A

Parameters:

Name Type Default Description
Required Parameters
srcRast VARCHAR N/A The name of the raster to generate information for

Helper Functions

This are functions that are also dependents of the Main functions. These ones likely have little use outside of their dependent use or are quite straightforward to understaand what they do.

FAIB_GET_DATA_TYPE

Description:

This function returns the data type of table column. Is used to determine whether a field is Numerice or Character to define whether to create Value Attribute Table or raster off of supplied column if Numeric.

FAIB_DATATYPE_ISNUM

Description:

This function returns a boolean (True) if the datatype is Numeric. Used in combination with FAIB_GET_DATA_TYPE.

FAIB_DATATYPE_ISCHAR

Description:

This function returns a boolean (True) if the datatype is Character. Used in combination with FAIB_GET_DATA_TYPE.

FAIB_FORMAT_RASTVALS

Description:

This function takes a text representation of raster values (‘1, 2, 3, 4-10, 20-25’) and returns a list of all the values. In other words, it formats the string to draw out the values with a range (x-y) and is used in the FAIB_RASTER_FROM_RASTER function to properly format a seledction to apply to queries to generate output raster.

FAIB_GET_RASTVALS_OPPOSITE

Description:

This function takes a text representation of raster values and a source raster and will return values in the raster that are not contained in hte supplied list. This is needed for doing masking and value reclassification in the FAIB_RASTER_FROM_RASTER function.